﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SQLite;

namespace DepartmentManagement.DataAccess
{
    /// <summary>
    /// SqlServer数据库访问类
    /// <remarks>
    /// <para>1. 所有数据库操作的访问都通过这个访问类来实现</para>
    /// <para>2. 这个访问类在不带参数构建时创建的是对人力资源数据的访问，需要访问其他数据库时需要调用带参数的构造方法。</para>
    /// </remarks>
    /// <example>
    /// tmpSqlExecuter=new WEC.Database.SqlHelper(<connStr>);
    /// System.Data.SqlClient.SQLiteParameter[] tmpParams=new System.Data.SqlClient.SQLiteParameter[3]; // 这里指定需要调用的参数的数量
    /// tmpParams[0]=tmpSqlExecuter.MakeInParam(<ParamName>, <ParamType>,<ParamValue>);
    /// tmpParams[1]=tmpSqlExecuter.MakeInParam(<ParamName>, <ParamType>,<ParamValue>);
    /// tmpParams[2]=tmpSqlExecuter.MakeReturnParam(<ParamName>, <ParamType>);
    /// System.Data.DataSet tmpDS=tmpSqlExecuter.ExecuteDataset(System.Data.CommandType.StoredProcedure, <StoreProcedureName>, tmpParams);
    /// </example>
    /// </summary>
    sealed class SqlHelper : IDisposable
    {
        private SQLiteConnection _conn;
        private SQLiteTransaction _trans;

        #region 构造函数
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns></returns>
        public static string GetSQLiteConnectionString()
        {
            return GetSQLiteConnectionString("DepartmentManagement");
        }
        /// <summary>
        /// 根据连接字符串关键字名获取连接字符串
        /// </summary>
        /// <param name="connstrKeyName"></param>
        /// <returns></returns>
        public static string GetSQLiteConnectionString(string connstrKeyName)
        {
            string dataSource = string.Empty;
            string dataFileName = System.Configuration.ConfigurationManager.AppSettings["DataFile"];
            bool isRelatedPath = Boolean.Parse(System.Configuration.ConfigurationManager.AppSettings["RelatedPath"]);
            string dataFilePath = System.Configuration.ConfigurationManager.AppSettings["DataFilePath"];
            string appRoot = System.Windows.Forms.Application.StartupPath;

            if (isRelatedPath)
            {
                // file path uses related path
                dataFilePath = appRoot + dataFilePath;
            }

            dataSource = dataFilePath + dataFileName;
            if (!System.IO.Directory.Exists(dataFilePath))
            {
                System.IO.Directory.CreateDirectory(dataFilePath);
            }

            if (!System.IO.File.Exists(dataSource))
            {
                SQLiteConnection.CreateFile(dataSource);
            }
            SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();

            connBuilder.DataSource = dataSource;
            connBuilder.Pooling = true;

            return connBuilder.ToString();
        }

        public SQLiteConnection Connection
        {
            get
            {
                return _conn;
            }
        }

        /// <summary>
        /// 通过dbCreateScript资源文件的内容初始化数据库的基本表结构
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private static bool initSqlDatabase(SQLiteConnection conn)
        {
            bool result = false;

            if (null != conn && ConnectionState.Open != conn.State)
                conn.Open();

            // Now ready to initialize database table structure
            //TODO: Need to implement a way to init tables views and trigers with sql script files



            conn.Close();
            return result;
        }

        /// <summary>
        /// 根据不同数据库来创建相应数据库连接
        /// </summary>
        /// <param name="database">代表不同数据库的对象，具体信息请参看WEC.Database下面Databases.cs文件</param>
        /// <example>
        /// WEC.Database.SqlHelper tmpSqlHelper = new WEC.Database.SqlHelper(WEC.Database.DatabaseProjectControl);
        /// </example>
        public SqlHelper()
        {
            string connStr = GetSQLiteConnectionString();
            _conn = new SQLiteConnection(connStr);

            // Need to be implemented in future
            //SqlHelper.initSqlDatabase(_conn);

            if (_conn.State != ConnectionState.Closed)
            {
                _conn.Close();
            }
        }

        public SqlHelper(string connstrKeyName)
        {
            string connStr = GetSQLiteConnectionString(connstrKeyName);
            _conn = new SQLiteConnection(connStr);
            if (_conn.State != ConnectionState.Closed)
            {
                _conn.Close();
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool bDispose)
        {
            if (!bDispose)
                return;

            if (_trans != null)
            {
                _trans.Dispose();
                _trans = null;
            }

            if (_conn.State != ConnectionState.Closed)
                _conn.Close();

            _conn.Dispose();
            _conn = null;
        }

        #endregion

        #region 私有方法
        /// <summary>
        /// 获取Command对象中输出或者返回的参数
        /// </summary>
        /// <param name="command">Command对象</param>
        /// <returns>输出或者返回的参数</returns>
        private List<SQLiteParameter> getOutputParameters(SQLiteCommand command)
        {
            
            if (command == null) throw new ArgumentNullException("command");

            List<SQLiteParameter> result = new List<SQLiteParameter>();

            foreach (SQLiteParameter sp in command.Parameters)
            {
                if (ParameterDirection.Output == sp.Direction ||
                    ParameterDirection.ReturnValue == sp.Direction)
                    // 添加一个Sql参数对象到结果列表中
                    result.Add(new SQLiteParameter(sp.ParameterName,
                        sp.DbType,
                        sp.Size,
                        sp.Direction,
                        byte.MinValue, // This parameter: precision will be ignored
                        byte.MinValue, // This parameter: scale will be ignored
                        sp.SourceColumn,
                        sp.SourceVersion,
                        sp.SourceColumnNullMapping,
                        sp.Value));
            }

            return result;
        }

        /// <summary>
        /// 将SQLiteParameter参数组值分配给SQLiteCommand
        /// </summary>
        /// <param name="command">SQLiteCommand</param>
        /// <param name="SQLiteParameter">SQLiteParameter参数组</param>
        private void attachParammetre(SQLiteCommand command, SQLiteParameter[] SQLiteParameter)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (SQLiteParameter != null)
            {
                foreach (SQLiteParameter param in SQLiteParameter)
                {
                    if (param != null)
                    {
                        //检查未分配的输出参数，将其值分配为DBNull.Value
                        if ((param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Input) &&
                            (param.Value == null))
                        {
                            param.Value = DBNull.Value;
                        }
                        command.Parameters.Add(param);
                    }
                }
            }
        }

        /// <summary>
        /// 预处理用户提供的命令，数据厍连接，命令类型，参数
        /// </summary>
        /// <param name="connection">数据厍连接</param>
        /// <param name="command">命令</param>
        /// <param name="commandType">命令类型（存储过程或文本）</param>
        /// <param name="commandText">存储过程名或SQL文本</param>
        /// <param name="parameter">与命令相关的参数</param>
        /// <param name="mustCloseConnection">如果命令是打开的则为True,其它情况下是false</param>
        private void prepareCommand(SQLiteConnection connection, SQLiteCommand command, CommandType commandType, string commandText, SQLiteParameter[] parameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
                mustCloseConnection = true;
            }
            else
            {
                mustCloseConnection = false;
            }

            command.Connection = connection;
            command.CommandType = commandType;
            command.CommandText = commandText;

            if (parameters != null)
            {
                attachParammetre(command, parameters);
            }

            return;
        }

        #endregion

        #region 生成参数

        /// <summary>
        /// 输入参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DataType">类型</param>
        /// <param name="Value">值</param>
        /// <returns>完整的sql数据厍参数</returns>
        public SQLiteParameter MakeInParam(string ParamName, SqlDbType DataType, object Value)
        {
            return MakeParam(ParamName, DataType, ParameterDirection.Input, Value);
        }

        /// <summary>
        /// 输出参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DataType">类型</param>
        /// <returns>完整的sql输出参数</returns>
        public SQLiteParameter MakeOutParam(string ParamName, SqlDbType DataType)
        {
            return MakeParam(ParamName, DataType, ParameterDirection.Output, null);
        }

        /// <summary>
        /// 返回参数
        /// </summary>
        /// <param name="ParamName">参数名</param>
        /// <param name="DataType">类型</param>
        /// <returns>完整的sql返回参数</returns>
        public SQLiteParameter MakeReturnParam(string ParamName, SqlDbType DataType)
        {
            return MakeParam(ParamName, DataType, ParameterDirection.ReturnValue, null);
        }
        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Direction">方向（输入参数、输出参数、返回值参数等）</param>
        /// <param name="Value">值</param>
        /// <returns>参数</returns>
        public SQLiteParameter MakeParam(string ParamName, SqlDbType DataType, ParameterDirection Direction, object Value)
        {
            SQLiteParameter param = new SQLiteParameter(ParamName, DataType);
            param.Direction = Direction;
            if (Direction != ParameterDirection.Output || Value != null)
            {
                param.Value = Value;
            }

            return param;
        }

        #endregion

        #region ExecuteNonQuery方法

        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(CommandType.Text, commandText);
        }

        public int ExecuteNonQuery(string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQuery(CommandType.Text, commandText, parameters);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, (SQLiteParameter[])null);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQuery(_conn, commandType, commandText, parameters);
        }

        public int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQuery(conn,CommandType.StoredProcedure,"PublishOrders", new SQLiteParameter[] parameters("@prodid",11))
        /// </remarks>
        /// <param name="connection">一个有效的数据厍连接对象</param>
        /// <param name="commandType">命令类型（存储过程、命令文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">SQLiteParameters参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQuery(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            int retval = command.ExecuteNonQuery();

            command.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }

        public int ExecuteNonQuery(out int id, string commandText)
        {
            return ExecuteNonQuery(out id, CommandType.Text, commandText);
        }

        public int ExecuteNonQuery(out int id, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQuery(out id, CommandType.Text, commandText, parameters);
        }

        public int ExecuteNonQuery(out int id, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(out id, commandType, commandText, (SQLiteParameter[])null);
        }

        public int ExecuteNonQuery(out int id, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQuery(out id, _conn, commandType, commandText, parameters);
        }

        public int ExecuteNonQuery(out int id, SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(out id, connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接的命令
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQuery(out id,conn,CommandType.Text,"select * from Test",(SQLiteParameters[])null);
        /// </remarks>
        /// <param name="id">输出参数</param>
        /// <param name="connection">有效的数据厍连接</param>
        /// <param name="commandType">命令类型（存储过程、文本命令）</param>
        /// <param name="commandText">存储过程名、SQL语句</param>
        /// <param name="parameters">SQL参数数组</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(out int id, SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            int retval = command.ExecuteNonQuery();
            command.Parameters.Clear();

            id = int.Parse(command.ExecuteScalar().ToString());

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }

        #endregion

        #region ExecuteNonQueryWithTransaction方法
        public int ExecuteNonQueryWithTrans(string commandText)
        {
            return ExecuteNonQueryWithTrans(CommandType.Text, commandText);
        }

        public int ExecuteNonQueryWithTrans(string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithTrans(CommandType.Text, commandText, parameters);
        }

        public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText)
        {
            return ExecuteNonQueryWithTrans(commandType, commandText, (SQLiteParameter[])null);
        }

        public int ExecuteNonQueryWithTrans(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithTrans(_conn, commandType, commandText, parameters);
        }

        public int ExecuteNonQueryWithTrans(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQueryWithTrans(connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接对象的命令(支持事务)
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQuery(conn,CommandType.StoredProcedure,"PublishOrders", new SQLiteParameter[] parameters("@prodid",11))
        /// </remarks>
        /// <param name="connection">一个有效的数据厍连接对象</param>
        /// <param name="commandType">命令类型（存储过程、命令文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">SQLiteParameters参数数组</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQueryWithTrans(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            int retval = 0;

            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            SQLiteTransaction trans = _conn.BeginTransaction();

            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            try
            {
                retval = command.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SQLiteException ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                command.Parameters.Clear();

                if (mustCloseConnection)
                {
                    connection.Close();
                }
            }
            return retval;
        }

        public int ExecuteNonQueryWithTrans(out int id, string commandText)
        {
            return ExecuteNonQueryWithTrans(out id, CommandType.Text, commandText);
        }

        public int ExecuteNonQueryWithTrans(out int id, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithTrans(out id, CommandType.Text, commandText, parameters);
        }

        public int ExecuteNonQueryWithTrans(out int id, CommandType commandType, string commandText)
        {
            return ExecuteNonQueryWithTrans(out id, commandType, commandText, (SQLiteParameter[])null);
        }

        public int ExecuteNonQueryWithTrans(out int id, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithTrans(out id, _conn, commandType, commandText, parameters);
        }

        public int ExecuteNonQueryWithTrans(out int id, SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteNonQueryWithTrans(out id, connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接的命令(支持事务)
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQuery(out id,conn,CommandType.Text,"select * from Test",(SQLiteParameters[])null);
        /// </remarks>
        /// <param name="id">输出参数</param>
        /// <param name="connection">有效的数据厍连接</param>
        /// <param name="commandType">命令类型（存储过程、文本命令）</param>
        /// <param name="commandText">存储过程名、SQL语句</param>
        /// <param name="parameters">SQL参数数组</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQueryWithTrans(out int id, SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            int retval = command.ExecuteNonQuery();
            command.Parameters.Clear();

            id = int.Parse(command.ExecuteScalar().ToString());

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }
        #endregion

        #region ExecuteMultipleNonQueryWithTrans方法
        public void CommitTransaction()
        {
            if (_trans == null) throw new ArgumentNullException("transaction");

            try
            {
                _trans.Commit();
            }
            catch (SQLiteException ex)
            {
                _trans.Rollback();
                throw ex;
            }
            finally
            {
                _trans.Dispose();
                _trans = null;
                if (_conn.State == ConnectionState.Open)
                {
                    _conn.Close();
                }
            }
        }

        public void RollbackTransaction()
        {
            if (_trans == null) throw new ArgumentNullException("transaction");

            _trans.Rollback();
            _trans.Dispose();
            _trans = null;

            if (_conn.State == ConnectionState.Open)
            {
                _conn.Close();
            }
        }

        public void ExecuteMultipleNonQueryWithTrans(string commandText)
        {
            ExecuteMultipleNonQueryWithTrans(CommandType.Text, commandText);
        }

        public void ExecuteMultipleNonQueryWithTrans(string commandText, params SQLiteParameter[] parameters)
        {
            ExecuteMultipleNonQueryWithTrans(CommandType.Text, commandText, parameters);
        }

        public void ExecuteMultipleNonQueryWithTrans(CommandType commandType, string commandText)
        {
            ExecuteMultipleNonQueryWithTrans(commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接对象的命令(支持事务)
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteMultipleNonQueryWithTrans(conn,CommandType.StoredProcedure,"PublishOrders", new SQLiteParameter[] parameters("@prodid",11))
        /// </remarks>
        /// <param name="commandType">命令类型（存储过程、命令文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">SQLiteParameters参数数组</param>
        public void ExecuteMultipleNonQueryWithTrans(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            int retval = 0;

            if (_trans == null)
            {
                if (_conn.State != ConnectionState.Open)
                {
                    _conn.Open();
                }
                _trans = _conn.BeginTransaction();
            }

            SQLiteCommand command = new SQLiteCommand();

            bool mustCloseConnection = false;
            prepareCommand(_conn, command, commandType, commandText, parameters, out mustCloseConnection);

            try
            {
                retval = command.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                _trans.Rollback();
                throw ex;
            }
            finally
            {
                command.Parameters.Clear();
            }
        }
        #endregion

        #region ExecuteNonQueryWithReturnParams方法
        public int ExecuteNonQueryWithReturnParams(string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(CommandType.Text, commandText, out retParams);
        }

        public int ExecuteNonQueryWithReturnParams(string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithReturnParams(CommandType.Text, commandText, out retParams, parameters);
        }

        public int ExecuteNonQueryWithReturnParams(CommandType commandType, string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(commandType, commandText, out retParams, (SQLiteParameter[])null);
        }

        public int ExecuteNonQueryWithReturnParams(CommandType commandType, string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithReturnParams(_conn, commandType, commandText, out retParams, parameters);
        }

        public int ExecuteNonQueryWithReturnParams(SQLiteConnection connection, CommandType commandType, string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(connection, commandType, commandText, out retParams, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQueryWithReturnParams(conn,CommandType.StoredProcedure,"PublishOrders", new SQLiteParameter[] parameters("@prodid",11))
        /// </remarks>
        /// <param name="connection">一个有效的数据厍连接对象</param>
        /// <param name="commandType">命令类型（存储过程、命令文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">SQLiteParameters参数数组</param>
        /// <param name="retParams">返回的参数列表</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteNonQueryWithReturnParams(SQLiteConnection connection, CommandType commandType, string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            int retval = command.ExecuteNonQuery();

            retParams = getOutputParameters(command);

            command.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }

        public int ExecuteNonQueryWithReturnParams(out int id, string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(out id, CommandType.Text, commandText, out retParams);
        }

        public int ExecuteNonQueryWithReturnParams(out int id, string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithReturnParams(out id, CommandType.Text, commandText, out retParams, parameters);
        }

        public int ExecuteNonQueryWithReturnParams(out int id, CommandType commandType, string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(out id, commandType, commandText, out retParams, (SQLiteParameter[])null);
        }

        public int ExecuteNonQueryWithReturnParams(out int id, CommandType commandType, string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            return ExecuteNonQueryWithReturnParams(out id, _conn, commandType, commandText, out retParams, parameters);
        }

        public int ExecuteNonQueryWithReturnParams(out int id, SQLiteConnection connection, CommandType commandType, string commandText, out List<SQLiteParameter> retParams)
        {
            return ExecuteNonQueryWithReturnParams(out id, connection, commandType, commandText, out retParams, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据厍连接的命令
        /// </summary>
        /// <remarks>
        /// 示例：
        /// int result=ExecuteNonQueryWithReturnParams(out id,conn,CommandType.Text,"select * from Test",(SQLiteParameters[])null);
        /// </remarks>
        /// <param name="id">输出参数</param>
        /// <param name="connection">有效的数据厍连接</param>
        /// <param name="commandType">命令类型（存储过程、文本命令）</param>
        /// <param name="commandText">存储过程名、SQL语句</param>
        /// <param name="parameters">SQL参数数组</param>
        /// <param name="retParams">返回的参数列表</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQueryWithReturnParams(out int id, SQLiteConnection connection, CommandType commandType, string commandText, out List<SQLiteParameter> retParams, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            int retval = command.ExecuteNonQuery();
            retParams = getOutputParameters(command);

            command.Parameters.Clear();

            id = int.Parse(command.ExecuteScalar().ToString());

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }
        #endregion

        #region ExecuteDataset方法

        public DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(CommandType.Text, commandText);
        }

        public DataSet ExecuteDataset(string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteDataset(CommandType.Text, commandText, parameters);
        }

        public DataSet ExecuteDataset(CommandType commandType, string commandText)
        {
            return ExecuteDataset(commandType, commandText, (SQLiteParameter[])null);
        }

        public DataSet ExecuteDataset(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteDataset(_conn, commandType, commandText, parameters);
        }

        public DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteDataset(connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库连接命令，指定存储过程，返回Dataset
        /// </summary>
        /// <param name="connection">有效的数据库连接</param>
        /// <param name="commandType">命令类型（存储过程、文本命令）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">SQL参数数组</param>
        /// <returns>Dataset</returns>
        public DataSet ExecuteDataset(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;
            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            using (SQLiteDataAdapter da = new SQLiteDataAdapter())
            {
                da.SelectCommand = command;
                DataSet ds = new DataSet();

                da.Fill(ds);

                command.Parameters.Clear();

                if (mustCloseConnection)
                {
                    connection.Close();
                }

                return ds;
            }
        }


        #endregion

        #region ExecuteReader方法

        public SQLiteDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(commandText, (SQLiteParameter[])null);
        }

        public SQLiteDataReader ExecuteReader(string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteReader(CommandType.Text, commandText, parameters);
        }

        public SQLiteDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(commandType, commandText, (SQLiteParameter[])null);
        }

        public SQLiteDataReader ExecuteReader(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            try
            {
                _conn.Open();

                return ExecuteReader(_conn, commandType, commandText, parameters);
            }
            catch
            {
                // If we fail to return the SqlDatReader, we need to close the connection ourselves
                if (_conn != null) _conn.Close();
                throw;
            }
        }

        public SQLiteDataReader ExecuteReader(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteReader(connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行指定数据库命令，指定存储过程，返回sqlDataReader
        /// </summary>
        /// <param name="connection">有效的数据库连接</param>
        /// <param name="commandType">命令类型（文本命令、存储过程）</param>
        /// <param name="commandText">sql语句或存储过程名</param>
        /// <param name="parameters">SQL参数数组</param>
        /// <returns>SqlDataReader</returns>
        public SQLiteDataReader ExecuteReader(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            SQLiteCommand command = new SQLiteCommand();

            try
            {
                prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

                SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                // 清除参数,以便再次使用..
                // HACK: There is a problem here, the output parameter values are fletched 
                // when the reader is closed, so if the parameters are detached from the command
                // then the SqlReader can't set its values. 
                // When this happen, the parameters can't be used again in other command.
                bool canClear = true;
                foreach (SQLiteParameter commandParameter in command.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                {
                    //cmd.Dispose();
                    command.Parameters.Clear();
                }

                return reader;
            }
            catch
            {
                if (mustCloseConnection)
                {
                    connection.Close();
                }
                throw;
            }
        }

        #endregion

        #region ExecuteScalar方法

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, (SQLiteParameter[])null);
        }

        public object ExecuteScalar(string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteScalar(CommandType.Text, commandText, parameters);
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, (SQLiteParameter[])null);
        }

        public object ExecuteScalar(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            return ExecuteScalar(_conn, commandType, commandText, parameters);
        }

        public object ExecuteScalar(SQLiteConnection connection, CommandType commandType, string commandText)
        {
            return ExecuteScalar(connection, commandType, commandText, (SQLiteParameter[])null);
        }

        /// <summary>
        /// 执行数据库连接的命令、存储过程，返回第一行第一列。
        /// </summary>
        /// <param name="connection">有效的数据库连接</param>
        /// <param name="commandType">命令类型（存储过程、SQL文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>第一行第一列</returns>
        public object ExecuteScalar(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            SQLiteCommand command = new SQLiteCommand();
            bool mustCloseConnection = false;

            prepareCommand(connection, command, commandType, commandText, parameters, out mustCloseConnection);

            object retval = command.ExecuteScalar();

            command.Parameters.Clear();

            if (mustCloseConnection)
            {
                connection.Close();
            }

            return retval;
        }

        #endregion

        #region 执行ExecuteScalar,将结果以字符串形式输出

        public string ExecuteScalarToString(CommandType commandType, string commandText)
        {
            object retval = ExecuteScalar(commandType, commandText);

            if (retval == null)
            {
                return "";
            }
            else
            {
                return retval.ToString();
            }
        }

        /// <summary>
        /// 执行ExecuteScalar,将结果以字符串形式输出
        /// </summary>
        /// <param name="commandType">命令类型（存储过程、命令文本）</param>
        /// <param name="commandText">存储过程名或SQL语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>第一行第一列的字符串形式</returns>
        public string ExecuteScalarToString(CommandType commandType, string commandText, params SQLiteParameter[] parameters)
        {
            object retval = ExecuteScalar(commandType, commandText, parameters);

            if (retval == null)
            {
                return "";
            }
            else
            {
                return retval.ToString();
            }
        }

        #endregion

    }
}
